package com.hxut.dao;

import com.hxut.model.Goods;
import com.hxut.model.PageBean;
import com.hxut.utils.C3p0Utils;
import com.hxut.utils.JdbcUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class GoodsDao {
    /*public ArrayList<Goods> findAllGoods(){
        //查询数据库中的所有产品信息
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        ArrayList<Goods> goodsList = new ArrayList<>();
        try {
                connection = JdbcUtils.getConnnect();
                //定义sql
                String sql = "select * from goods";
                preparedStatement = connection.prepareStatement(sql);
                resultSet = preparedStatement.executeQuery();
                while(resultSet.next()){
                    Goods good = new Goods();
                    good.setId(resultSet.getInt("id"));
                    good.setName(resultSet.getString("name"));
                    good.setCover(resultSet.getString("cover"));
                    good.setImage1(resultSet.getString("image1"));
                    good.setImage2(resultSet.getString("image2"));
                    good.setPrice(resultSet.getFloat("price"));
                    good.setIntro(resultSet.getString("intro"));
                    good.setStock(resultSet.getInt("stock"));
                    goodsList.add(good);
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(resultSet, preparedStatement,connection);
        }
        return goodsList;

    }*/

    public ArrayList<Goods> findAllGoods(){
        ArrayList<Goods> goodsList;
        QueryRunner queryRunner = new QueryRunner(C3p0Utils.getDataSource());
        String sql = "select * from goods";
        try {
            goodsList = (ArrayList) queryRunner.query(sql, new BeanListHandler<Goods>(Goods.class));
            return  goodsList;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;

    }

    /*public Goods findGoodsById(int id){
        //查询数据库中的产品信息通过id
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = JdbcUtils.getConnnect();
            //定义sql
            String sql = "select * from goods where id = ?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,id);
            resultSet = preparedStatement.executeQuery();
            Goods good = new Goods();
            while(resultSet.next()){
                good.setId(resultSet.getInt("id"));
                good.setName(resultSet.getString("name"));
                good.setCover(resultSet.getString("cover"));
                good.setImage1(resultSet.getString("image1"));
                good.setImage2(resultSet.getString("image2"));
                good.setPrice(resultSet.getFloat("price"));
                good.setIntro(resultSet.getString("intro"));
                good.setStock(resultSet.getInt("stock"));
            }return good;
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(resultSet, preparedStatement,connection);
        }

        return null;
    }*/
    public Goods findGoodsById(int id){
        QueryRunner queryRunner = new QueryRunner(C3p0Utils.getDataSource());
        String sql = "select * from goods where id = ?";
        try {
            Goods goods = queryRunner.query(sql, new BeanHandler<Goods>(Goods.class), id);
            return goods;
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return null;
    }

    public int addGoods(Goods goods){
        QueryRunner queryRunner = new QueryRunner(C3p0Utils.getDataSource());
        String sql= "insert into cakesdb.goods (name, cover, image1, image2, price, intro, stock,type_id) values (?,?,?,?,?,?,?,?)";
        Object[] params = new Object[]{goods.getName(),goods.getCover(),goods.getImage1(),goods.getImage2(),goods.getPrice(),goods.getIntro(),goods.getStock(),1};

        try {
            int rows = queryRunner.update(sql, params);
            return rows;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    public int deleteGoodById(int id){
        QueryRunner queryRunner = new QueryRunner(C3p0Utils.getDataSource());
        String sql = "delete from cakesdb.goods where id =?";
        try {
            int rows = queryRunner.update(sql, id);
            return rows;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }


    public int findGoodsCount(){
        QueryRunner queryRunner = new QueryRunner(C3p0Utils.getDataSource());
        String sql = "select count(*) as count from goods";
        try {
            Long result = (Long) queryRunner.query(sql, new ScalarHandler<Object>("count"));
            return result.intValue();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    /**
     *
     * @param currentPage 展示的第几页数据
     * @param currentCount 每页展示的数据的条数
     * @return PageBean
     */
    public PageBean findGoodsByPage(int currentPage, int currentCount) {
        PageBean pageBean = new PageBean();
        int totalCount = findGoodsCount();
        /*
        Math.ceil向上取整
        totalCount*1.0转换为小数
         */
        int totalPage = (int) Math.ceil((totalCount*1.0)/currentCount);  //总页数 = 数据总条数/每页展示的数据条数(向上取整)
        pageBean.setCurrentPage(currentPage);
        pageBean.setTotalPage(totalPage);
        pageBean.setCurrentCount(currentCount);
        pageBean.setTotalCount(totalCount);

        QueryRunner queryRunner = new QueryRunner(C3p0Utils.getDataSource());
        int start = (currentPage-1)*currentCount; //(当前页码-1)*每页数据
        String sql = "select * from goods order by id limit ?,?";
        Object[] params = new Object[]{start,currentCount};

        try {
            ArrayList<Goods> goodsList = new ArrayList<Goods>();
            goodsList = (ArrayList<Goods>) queryRunner.query(sql, new BeanListHandler<Goods>(Goods.class), params);
            pageBean.setGoodsList(goodsList);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return pageBean;
    }
}
